/**   
 * Copyright © 2019 eSunny Info. Tech Ltd. All rights reserved.
 * 
 * @Description:TODO(功能)
 * @className OrderDaoImpl.java
 * @Package: com.erp.dao 
 * @author: yj   
 * @date: 2019年2月21日 下午7:28:23 
 */
package com.erp.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.erp.po.Customers;
import com.erp.po.Orders;
import com.erp.util.Page;


/**
 * @className OrderDaoImpl.java
 * @author yj
 * @Description:TODO(功能)
 * @Date 2019年2月21日
 */
public class OrderDaoImpl extends BaseDao implements OrderDao {

	/* 
	 * 使用ajax 查询product数据
	 */
	@Override
	public ResultSet selectProducts(Connection conn) throws SQLException {
		String sql = "select * from products";
		return super.selectData(conn, sql, null);
	}

	/* 
	 * 新建订单
	 */
	@Override
	public int insertOrder(Connection conn, Orders order) throws SQLException {
		String sql = "insert into ORDERS(ORDERID, ORDER_DATE, customerid, empid) values(?,to_date(?,'yyyy-MM-dd'),?,?)";
		Object[] obj = {order.getOrderID(), order.getOrderDate(), 
				order.getCustomer().getCustomerID(),order.getEmployees().getEmpID()};
		return super.updateData(conn, sql, obj);
	}

	/* 
	 * 使用ajax 查询emp数据
	 */
	@Override
	public ResultSet selectEmps(Connection conn) throws SQLException {
		String sql = "select * from employees";
		return super.selectData(conn, sql, null);
	}

	/* 
	 * 使用ajax 查询custs数据
	 */
	@Override
	public ResultSet selectCusts(Connection conn) throws SQLException {
		String sql = "select * from customers";
		return super.selectData(conn, sql, null);
	}

	/* 
	 * 使用ajax 查询order数据[分页]
	 */
	@Override
	public ResultSet selectOrdersByPage(Connection conn, Page<Orders> page) throws SQLException {
		String sql = "select * from \r\n" + 
				"	(select rownum rn, ord.ORDERID,to_char(ord.ORDER_DATE,'yyyy-MM-dd') order_date, emp.emp_name , cust.customer_name from orders ord , employees emp , customers cust where\r\n" + 
				"	ord.customerid = cust.customerid \r\n" + 
				"    and ord.empid = emp.empid \r\n" + 
				"    and rownum <= ? ORDER BY orderID) temp \r\n" + 
				"    where temp.rn > ? ";
		Object[] obj = { (page.getCurrentPage() * page.getPageSize()), (page.getCurrentPage() - 1) * page.getPageSize() };
		return super.selectData(conn, sql, obj);
	}

	/* 
	 * 查询order的总记录数
	 */
	@Override
	public ResultSet selectOrdersByTotal(Connection conn) throws SQLException {
		String sql = "select count(orderID) from orders";
		return super.selectData(conn, sql, null);
	}

	/* 
	 * 使用ajax 根据条件查询order数据[分页]
	 */
	@Override
	public ResultSet selectOrdersByCondition(Connection conn, Page<Orders> page, Orders ord) throws SQLException {
		ArrayList list = new ArrayList();
		String sql = "select * from \r\n" + 
				"        (select rownum rn, ord.ORDERID,to_char(ord.ORDER_DATE,'yyyy-MM-dd') "
				+ " order_date, emp.emp_name , cust.customer_name from orders ord , employees emp , customers cust where ";
		
		if(ord.getOrderID() != null && !("".equals(ord.getOrderID()))) {
			sql = sql.concat(" ord.orderid = ? and ");
			System.out.println(ord.getOrderID()+"----");
			list.add(ord.getOrderID());
		}
		if(ord.getOrderDate() != null && !("".equals(ord.getOrderDate()))) {
			sql = sql.concat(" ord.order_date = to_date(?,'yyyy-MM-dd') and ");
			list.add(ord.getOrderDate());
		}
		if(ord.getCustomer().getCustomerID() != 0) {
			sql = sql.concat(" ord.customerid = ? and ");
			list.add(ord.getCustomer().getCustomerID());
		}
		if(ord.getEmployees().getEmpID() != 0) {
			sql = sql.concat(" ord.empid = ? and ");
			list.add(ord.getEmployees().getEmpID());
		}
		
		sql = sql.concat(" ord.customerid = cust.customerid \r\n" + 
				"        and ord.empid = emp.empid \r\n" + 
				"        and rownum <= ? ORDER BY orderID) temp \r\n" + 
				"        where temp.rn > ? ");
		System.out.println(sql);
		list.add(page.getCurrentPage() * page.getPageSize());
		list.add((page.getCurrentPage() - 1) * page.getPageSize());
		return super.selectData(conn, sql, list.toArray());
	}

	/* 
	 * 根据条件查询order的总记录数
	 */
	@Override
	public ResultSet selectConditionByTotal(Connection conn , Orders ord) throws SQLException {
		ArrayList list = new ArrayList();
		String sql = "select count(orderid) from orders ord where 1=1 ";
		if(ord.getOrderID() != null && !("".equals(ord.getOrderID()))) {
			sql = sql.concat(" and  ord.orderid = ? ");
			list.add(ord.getOrderID());
		}
		if(ord.getOrderDate() != null && !("".equals(ord.getOrderDate()))) {
			sql = sql.concat(" and ord.order_date = to_date(?,'yyyy-MM-dd') ");
			list.add(ord.getOrderDate());
		}
		if(ord.getCustomer().getCustomerID() != 0) {
			sql = sql.concat(" and ord.customerid = ? ");
			list.add(ord.getCustomer().getCustomerID());
		}
		if(ord.getEmployees().getEmpID() != 0) {
			sql = sql.concat(" and ord.empid = ? ");
			list.add(ord.getEmployees().getEmpID());
		}
		return super.selectData(conn, sql, list.toArray());
	}

	/* 
	 * 修改订单
	 */
	@Override
	public int updateOrder(Connection conn, Orders order) throws SQLException {
		String sql = "update orders set \r\n" + 
				"       order_date = to_date(?,'yyyy-MM-dd') ,\r\n" + 
				"       customerid = ? ,\r\n" + 
				"       empid = ? \r\n" + 
				" where \r\n" + 
				"       orderid = ?";
		Object[] obj = { order.getOrderDate() , order.getCustomer().getCustomerID() , 
							order.getEmployees().getEmpID() , order.getOrderID()};
		return updateData(conn, sql, obj);
	}

	/* 
	 * 根据 orderId 删除订单
	 */
	@Override
	public int deleteOrderByOrderId(Connection conn, String orderId) throws SQLException {
		String sql = "DELETE FROM orders where orderid = ? ";
		return super.updateData(conn, sql, orderId);
	}

}
